from datetime import datetime
import pandas as pd

'''
自动生成SQL
'''

if __name__ == '__main__':
    type_dict = {
        'Int': 'int',
        'Float': 'float',
        'Double': 'double',
        'String': 'varchar',
        'DATE': 'datetime'
    }



    # 读取多个工作表
    file_path = 'field_data_20240605.xlsx'
    sheets = pd.read_excel(file_path, sheet_name=None)  # sheet_name=None表示读取所有工作表
    ans = ''
    # 打印每个工作表的数据
    for sheet_name, df in sheets.items():
        base_sql = "CREATE TABLE `{}` (".format(sheet_name)
        base_sql += '\n'
        base_sql += '\t'
        data_dict = df.to_dict()
        data_field = list(data_dict['数据字段'].values())
        data_name = list(data_dict['数据名称'].values())
        data_type = list(data_dict['数据类型'].values())
        data_length = list(data_dict['数据长度'].values())
        data_need = list(data_dict['是否必传'].values())
        data_example = list(data_dict['模拟数据'].values())
        n = len(data_field)
        for i in range(n):
            if isinstance(data_field[i], float):
                break
            cur_type = type_dict[data_type[i]]
            if data_type[i] == 'String':
                cur_type = 'varchar' + '({})'.format(int(data_length[i]))
            is_null = 'NOT NULL' if '是' == data_need[i] else 'NULL'
            if isinstance(data_example[i], datetime):
                cur_comment = data_name[i] + '。示例：' + data_example[i].strftime('%Y-%m-%d')
            else:
                cur_comment = data_name[i] + '。示例：' + str(data_example[i])
            row_sql = '`' + data_field[i] + '` ' + cur_type + ' ' + is_null + ' ' + 'COMMENT' + " '" + cur_comment + "',"
            base_sql += row_sql
            base_sql += '\n\t'
        base_sql += 'PRIMARY KEY (`{}`)'.format(data_field[0])
        base_sql += '\n);'
        ans += base_sql
        ans += '\n'
    with open('hadian.sql', 'w', encoding='utf-8') as file:
        file.write(ans)
    file.close()
